import os
import time
import datetime
import requests
import re
import csv
import pymysql


#构建ARK下载函数
def ark_cashinout_dwnldtomysql(ark_fundflowhtmladdress,cd):
    # 设定参数
    url = 'https://etfdb.com/etf/'+cd
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.99 Safari/537.36"}
    text = requests.get(url=url, headers=headers).content.decode('utf-8')

    # 为防止多次爬取对网站造成负担，打开网站后对内容做存储，如果当天文件存在则不再进行二次抓取
    if not os.path.exists(ark_fundflowhtmladdress+cd+'_web_html.txt'):
        text = requests.get(url=url).content.decode('utf-8')
        # print(text)
        with open(ark_fundflowhtmladdress+cd+'_web_html.txt', 'w', encoding='utf-8') as f:
            f.write(text)

    # 直接读取本地存储的txt文件
    with open(ark_fundflowhtmladdress+cd+'_web_html.txt', 'r', encoding='utf-8') as f:
        html = f.read()

    # 正则筛选
    pattern = re.compile('\[\d{13}.*?\]', re.S)
    fundflow_str = pattern.findall(html)
    # 获取的fundflow_str格式为['[1414972800000, 0.0]', '[1415059200000, 0.0]', '[1415145600000, 0.0]',...

    # 处理爬取的日期和资金流数据，及存入mysql数据库
    # 打开数据库连接
    db = pymysql.connect(host='localhost', user='root', password='123456', database='usfund_ark')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    ark_cashinout = []
    for oneday_fundflow_str in fundflow_str:
        # 将1414972800000改为date格式
        date = time.strftime("%Y-%m-%d", time.localtime(int(oneday_fundflow_str[1:14]) / 1000))
        # 将0.0改为float格式，并将单位改为美元，之前是百万美元
        fundflow = float(oneday_fundflow_str[16:-1]) * 1000000

        # 为减少资源消耗，只处理大于2021-01-01的资金流数据
        if date>'2020-01-01':
            ## 步骤（1）如果数据库没有那一条记录对应“date”，则存在csv文件夹中
            # 首先检查数据库是否有该日期，如果没有则插入
            sql = "SELECT count(*) FROM ark_fundvaluecashintout where date=\'" + date + "\'"
            # print(sql)
            try:
                # 执行SQL语句
                cursor.execute(sql)
                # 获取对应日期记录列表datercdnum
                datecnt = cursor.fetchone()[0]
            except:
                print("Error: unable to fetch data")
            # 如果数据库没有该日期，插入
            if datecnt == 0:
                sql = "INSERT INTO ark_fundvaluecashintout (date) VALUES(\'" + date + "\')"
                # print(sql)
                try:
                    # 执行sql语句
                    cursor.execute(sql)
                    # 提交到数据库执行
                    db.commit()
                except:
                    # 如果发生错误则回滚
                    db.rollback()

            ## 步骤（2）填充mysql ark cashinout相关数据
            # SQL 查询数据库date对应的日期数据是否为空，不为空情况为0或者有数字
            sql = "SELECT " + cd + "_cashinout FROM ark_fundvaluecashintout where date=\'" + date + "\'"
            # print(sql)
            try:
                # 执行SQL语句
                cursor.execute(sql)
                # 获取对应日期记录列表datercdnum
                cddatefundflownum = cursor.fetchone()[0]
            except:
                print("Error: unable to fetch data")

            # 如果数据库中没有这一天的记录，则
            if cddatefundflownum is None:
                # 统一增添到ark_cashinout表中
                ark_cashinout.append((date, fundflow))
                # update 相应的ETF对应日期申赎数据
                sql = "UPDATE ark_fundvaluecashintout set " + cd + "_cashinout=" + str(fundflow) + " where date=\'" + date + "\'"
                try:
                    # 执行sql语句
                    cursor.execute(sql)
                    # 提交到数据库执行
                    db.commit()
                except:
                    # 如果发生错误则回滚
                    db.rollback()

                # update 加总combined_cashinout资金申赎数据
                sql = "UPDATE ark_fundvaluecashintout set combined_cashinout=if(arkf_cashinout is null,0,arkf_cashinout)" \
                      "+if(arkg_cashinout is null,0,arkg_cashinout)" \
                      "+if(arkk_cashinout is null,0,arkk_cashinout)" \
                      "+if(arkq_cashinout is null,0,arkq_cashinout)" \
                      "+if(arkw_cashinout is null,0,arkw_cashinout)" \
                      "+if(arkx_cashinout is null,0,arkx_cashinout)" \
                      "+if(prnt_cashinout is null,0,prnt_cashinout)" \
                      "+if(izrl_cashinout is null,0,izrl_cashinout)" \
                      "where date=\'" + date + "\'"
                try:
                    # 执行sql语句
                    cursor.execute(sql)
                    # 提交到数据库执行
                    db.commit()
                except:
                    # 如果发生错误则回滚
                    db.rollback()

            ## 步骤（3）填充mysql ark fundvalue相关数据
            # SQL 查询数据库date对应的日期数据是否为空，不为空情况为0或者有数字
            sql = "SELECT " + cd + "_fundvalue FROM ark_fundvaluecashintout where date=\'" + date + "\'"
            # print(sql)
            try:
                # 执行SQL语句
                cursor.execute(sql)
                # 获取对应日期记录列表datercdnum
                cddatefundvaluenum = cursor.fetchone()[0]
            except:
                print("Error: unable to fetch data")

            # 如果数据库中没有这一天的记录，则
            if cddatefundvaluenum is None:
                # update 相应的ETF对应日期资金规模数据
                # 查询mysql相应ETF对应日期资金规模数据
                sql="select sum(value_usd) from (select * from ark_holdings where date=\'"+ date +"\' and fund=\'"+cd.upper()+"\')t"
                # print(sql) #检查正确select sum(value_usd) from (select * from ark_holdings where date='2021-05-06' and fund='ARKF')t
                try:
                    # 执行SQL语句
                    cursor.execute(sql)
                    # 获取对应日期记录列表datercdnum
                    sumcddatefundvaluenum = cursor.fetchone()[0]
                    # print(cddatefundvaluenum) #检查结果正确 3901068215.71875
                except:
                    print("Error: unable to fetch data")

                # update mysql相应ETF对应日期资金规模数据
                sql = "UPDATE ark_fundvaluecashintout set " + cd + "_fundvalue=" + str(sumcddatefundvaluenum) + " where date=\'" + date + "\'"
                try:
                    # 执行sql语句
                    cursor.execute(sql)
                    # 提交到数据库执行
                    db.commit()
                except:
                    # 如果发生错误则回滚
                    db.rollback()

                # update 加总combined_cashinout资金申赎数据
                sql = "UPDATE ark_fundvaluecashintout set combined_fundvalue=if(arkf_fundvalue is null,0,arkf_fundvalue)" \
                      "+if(arkg_fundvalue is null,0,arkg_fundvalue)" \
                      "+if(arkk_fundvalue is null,0,arkk_fundvalue)" \
                      "+if(arkq_fundvalue is null,0,arkq_fundvalue)" \
                      "+if(arkw_fundvalue is null,0,arkw_fundvalue)" \
                      "+if(arkx_fundvalue is null,0,arkx_fundvalue)" \
                      "+if(prnt_fundvalue is null,0,prnt_fundvalue)" \
                      "+if(izrl_fundvalue is null,0,izrl_fundvalue)" \
                      "where date=\'" + date + "\'"
                try:
                    # 执行sql语句
                    cursor.execute(sql)
                    # 提交到数据库执行
                    db.commit()
                except:
                    # 如果发生错误则回滚
                    db.rollback()

    ## 步骤（4）将外部数据记录到csv文件
    # 将处理好的日期和资金数据存入csv数据表，如果csv原来有数据，再直接清空写入下面的新数据
    with  open(ark_fundflowhtmladdress+cd+' Cashinout.csv', 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerows(ark_cashinout)

    #爬虫间隔5秒
    time.sleep(5)

# 创建文件夹的函数
def mkdir(path):
    # 去除首位空格
    path = path.strip()
    # 去除尾部 \ 符号
    path = path.rstrip("\\")

    # 判断路径是否存在
    # 存在     True
    # 不存在   False
    isExists = os.path.exists(path)

    # 判断结果
    if not isExists:
        # 如果不存在则创建目录
        # 创建目录操作函数
        os.makedirs(path)
        print(path + ' 创建成功')
        return True
    else:
        # 如果目录存在则不创建，并提示目录已存在
        print(path + ' 目录已存在')
        return False


list=['arkf','arkg','arkk','arkq','arkq','arkw','arkx','prnt','izrl']

# 定义存储ark fundflow的文件夹地址
ark_fundflowhtmladdress="d:\\arkfundflow\\"+str(datetime.date.today())+"\\";
#创建ark抓取fundflow当天的文件夹d:\arkfundflow\2021-05-12 创建成功
mkdir(ark_fundflowhtmladdress)

for cd in list:
    ark_cashinout_dwnldtomysql(ark_fundflowhtmladdress,cd)
    print(cd+' etf 处理完毕')



